﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Text.RegularExpressions;

namespace LFramework.Data
{
    /// <summary>
    /// 对DataTable的扩展
    /// </summary>
    public static class DataTableExtension
    {
        /// <summary>
        /// 将Null String转换为空值
        /// </summary>
        /// <param name="dt"></param>
        public static void Null2EmptyString(this DataTable dt)
        {
            IList needTrimColumn = new ArrayList();

            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.DataType == typeof(string))
                {
                    dc.ReadOnly = false;
                    needTrimColumn.Add(dc);
                }
            }

            if (needTrimColumn.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in needTrimColumn)
                    {
                        if (dr[dc] == null)
                            dr[dc] = "";
                        else
                            dr[dc] = dr[dc].ToString().Trim();
                    }
                }
            }
        }


        /// <summary>
        /// 将Null String转换为空格，Oracle用
        /// </summary>
        /// <param name="dt"></param>
        public static void Null2Space(this DataTable dt)
        {
            IList needTrimColumn = new ArrayList();

            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.DataType == typeof(string))
                {
                    dc.ReadOnly = false;
                    needTrimColumn.Add(dc);
                }
            }

            if (needTrimColumn.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in needTrimColumn)
                    {
                        if (dr[dc] == null)
                            dr[dc] = " ";
                        else if (dr[dc].ToString() == "")
                            dr[dc] = " ";
                        else
                            dr[dc] = dr[dc].ToString();
                    }
                }
            }
        }

        public static string BuildCreateTableSQL(this DataTable dt, string tableName, DbTypeEnum dbtype)
        {
            switch (dbtype)
            {
                case DbTypeEnum.SqlServer:
                    return BuildCreateTableSQL_SqlServer(dt, tableName);
                case DbTypeEnum.Oracle:
                    return BuildCreateTableSQL_Oracle(dt, tableName);
                default:
                    break;
            }

            return "";
        }


        #region SqlServer

        /// <summary>
        /// 根据DataTable生成建表语句
        /// </summary>
        /// <param name="dt"></param>
        public static string BuildCreateTableSQL_SqlServer(DataTable dt, string tableName)
        {
            string sSql = "Create Table " + tableName + "(";
            foreach (DataColumn col in dt.Columns)
            {
                DataSet da = new DataSet();
                int iMaxLen = 100;
                if (col.DataType.Name.ToLower() == "string")
                {
                    foreach (DataRow theRow in dt.Rows)
                    {
                        if (theRow[col].ToString().Length > iMaxLen)
                        {
                            iMaxLen = theRow[col].ToString().Length;
                        }
                    }
                }
                iMaxLen = iMaxLen * 2;
                sSql = sSql + col.ColumnName + "  " + ConvertTypeToDbType_SqlServer(col.DataType, iMaxLen) + ",";
            }
            sSql = sSql.Substring(0, sSql.Length - 1);
            sSql = sSql + ")";

            return sSql;
        }


        private static string ConvertTypeToDbType_SqlServer(Type theType, int iLen)
        {
            string str = "varchar(200)";
            switch (theType.Name.ToLower())
            {
                case "string":
                    if (iLen < 4000)
                    {
                        str = "varchar(" + iLen.ToString() + ")";
                    }
                    else
                    {
                        str = "text";
                    }

                    break;
                case "16":
                    str = "int";
                    break;
                case "int32":
                    str = "int";
                    break;
                case "64":
                    str = "int";
                    break;
                case "int":
                    str = "int";
                    break;
                case "double":
                    str = "float";
                    break;
                case "float":
                    str = "float";
                    break;
                case "byte":
                    str = "image";
                    break;
                case "byte[]":
                    str = "image";
                    break;
                default:
                    str = "varchar(200)";
                    break;
            }
            return str;
        }

        #endregion

        #region SqlServer

        /// <summary>
        /// 根据DataTable生成建表语句
        /// </summary>
        /// <param name="dt"></param>
        public static string BuildCreateTableSQL_Oracle(DataTable dt, string tableName)
        {
            string sSql = "Create global temporary table " + tableName + "(";
            foreach (DataColumn col in dt.Columns)
            {
                DataSet da = new DataSet();
                int iMaxLen = 100;
                if (col.DataType.Name.ToLower() == "string")
                {
                    foreach (DataRow theRow in dt.Rows)
                    {
                        if (theRow[col].ToString().Length > iMaxLen)
                        {
                            iMaxLen = theRow[col].ToString().Length;
                        }
                    }
                }
                iMaxLen = iMaxLen * 2;
                sSql = sSql + col.ColumnName + "  " + ConvertTypeToDbType_Oracle(col.DataType, iMaxLen) + ",";
            }
            sSql = sSql.Substring(0, sSql.Length - 1);
            sSql = sSql + ")  ON COMMIT PRESERVE ROWS";

            return sSql;
        }


        private static string ConvertTypeToDbType_Oracle(Type theType, int iLen)
        {
            string str = "varchar2(200)";
            switch (theType.Name.ToLower())
            {
                case "string":
                    if (iLen < 4000)
                    {
                        str = "varchar2(" + iLen.ToString() + ")";
                    }
                    else
                    {
                        str = "clob";
                    }

                    break;
                case "16":
                    str = "integer";
                    break;
                case "int32":
                    str = "integer";
                    break;
                case "64":
                    str = "integer";
                    break;
                case "int":
                    str = "integer";
                    break;
                case "double":
                    str = "number(25,10)";
                    break;
                case "float":
                    str = "number(25,10)";
                    break;
                case "byte":
                    str = "clob";
                    break;
                case "byte[]":
                    str = "clob";
                    break;
                default:
                    str = "varchar2(200)";
                    break;
            }
            return str;
        }

        #endregion

    }
}
